package com.enfi.api.modules.bz.service.impl;


import com.enfi.api.modules.bz.domain.db.*;
import com.enfi.api.modules.bz.service.DataSourceService;
import com.enfi.api.modules.db.entity.Database;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.ShowStatement;
import net.sf.jsqlparser.statement.Statements;
import net.sf.jsqlparser.statement.select.Select;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * @author wind
 * @date 2020/3/12
 */
@Service
public class DataSourceServiceImpl implements DataSourceService {
    /**
     public static final String mysql = "com.mysql.jdbc.Driver";
     public static final String oracle = "oracle.jdbc.driver.OracleDriver";
     public static final String sqlserver = "net.sourceforge.jtds.jdbc.Driver";
     public static final String db2 = "com.ibm.db2.jcc.DB2Driver";
     public static final String psql = "org.postgresql.Driver";
     public static final String hive = "org.apache.hive.jdbc.HiveDriver";
     public static final String kylin = "org.apache.kylin.jdbc.Driver";
     **/
    /**
     * public static final String showTables_mysql = "show tables";
     * public static final String showTables_oracle = "select table_name from tabs";
     * public static final String showTables_sqlser = "select name from sysobjects where xtype='U' order by name";
     * public static final String showTables_db2 = "select name from sysibm.systables where type='T' and creator='$0'";
     * public static final String showTables_psql = "select tablename from pg_tables where tableowner='$0'";
     * public static final String showTables_hive = "show tables";
     * public static final String showTables_kylin = "show tables";
     **/
    public static Map<String, String> DRIVER_MAP = Maps.newHashMap();
    public static Map<String, String> PREFIX_MAP = Maps.newHashMap();
    public static Map<String, String> SHOWTABLE_MAP = Maps.newHashMap();

    static {
        DRIVER_MAP.put("MYSQL", "com.mysql.jdbc.Driver");
        PREFIX_MAP.put("MYSQL", "jdbc:mysql://");
        SHOWTABLE_MAP.put("MYSQL", "show tables");
    }
    @Override
    public List getTables(Database ds, TableSearchVo tableSearchVo) throws Exception {
        final List<Map> ret1 = Lists.newArrayList();
        Connection conn = null;
        try {
            conn = getConnection(ds);
            ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), tableSearchVo.getSchemaPattern(), tableSearchVo.getTableNamePattern(), tableSearchVo.getTypes());
            while (rs.next()) {
                Map map = Maps.newHashMap();
                map.put("name", rs.getString(3));
                map.put("type", rs.getString(4));
                map.put("remarks", rs.getString("REMARKS"));
                ret1.add(map);
            }
        } finally {
            close(conn);
        }
        return ret1;

    }

    @Override
    public List getColumns(Database ds, ColumnSearchVo columnSearchVo) throws Exception {
        Connection conn = null;
        List<ColumnVo> list = Lists.newArrayList();
        try {
            conn = getConnection(ds);
            ResultSet rs = conn.getMetaData().getColumns(conn.getCatalog(), columnSearchVo.getSchemaPattern(), columnSearchVo.getTableNamePattern(), columnSearchVo.getColumnNamePattern());
            while (rs.next()) {
                ColumnVo columnVo = new ColumnVo();
                columnVo.setColumnName(rs.getString("COLUMN_NAME"));
                columnVo.setTypeName(rs.getString("TYPE_NAME"));
                columnVo.setColumnSize(rs.getInt("COLUMN_SIZE"));
                columnVo.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
                columnVo.setNullable(rs.getInt("NULLABLE"));
                columnVo.setTableName(rs.getString("TABLE_NAME"));
                columnVo.setTableCat(rs.getString("TABLE_CAT"));
                columnVo.setColumnDef(rs.getString("COLUMN_DEF"));
                columnVo.setRemarks(rs.getString("REMARKS"));
                list.add(columnVo);
            }
        } finally {
            close(conn);
        }
        return list;
    }
    @Override
    public List getPrimaryKeys(Database ds, PrimarySearchVo primarySearchVo) throws Exception {
        Connection conn = null;
        List<PrimaryKeyVo> list = Lists.newArrayList();
        try {
            conn = getConnection(ds);
            ResultSet rs = conn.getMetaData().getPrimaryKeys(primarySearchVo.getCatalog(), primarySearchVo.getSchema(), primarySearchVo.getTable());
            while (rs.next()) {
                PrimaryKeyVo primaryKeyVo = new PrimaryKeyVo();
                primaryKeyVo.setTableCat(rs.getString(1));
                primaryKeyVo.setTableSchema(rs.getString(2));
                primaryKeyVo.setTableName(rs.getString(3));
                primaryKeyVo.setColumnName(rs.getString(4));
                primaryKeyVo.setKeySeq(rs.getInt(5));
                primaryKeyVo.setPkName(rs.getString(6));
                list.add(primaryKeyVo);
            }
        } finally {
            close(conn);
        }
        return list;
    }
    @Override
    public List getForeignKeys(Database ds, ForeignKeySearchVo foreignKeySearchVo) throws Exception {
        Connection conn = null;
        List<ForeignKeyVo> list = Lists.newArrayList();
        try {
            conn = getConnection(ds);
            ResultSet rs = conn.getMetaData().getImportedKeys(foreignKeySearchVo.getCatalog(), foreignKeySearchVo.getSchema(), foreignKeySearchVo.getTable());
            while (rs.next()) {
                ForeignKeyVo foreignKeyVo = new ForeignKeyVo();
                foreignKeyVo.setPkTableCat(rs.getString("PKTABLE_CAT"));
                foreignKeyVo.setPkTableSchema(rs.getString("PKTABLE_SCHEM"));
                foreignKeyVo.setPkTableName(rs.getString("PKTABLE_NAME"));
                foreignKeyVo.setPkColumnName(rs.getString("PKCOLUMN_NAME"));

                foreignKeyVo.setFkTableCat(rs.getString("FKTABLE_CAT"));
                foreignKeyVo.setFkTableSchema(rs.getString("FKTABLE_SCHEM"));
                foreignKeyVo.setFkTableName(rs.getString("FKTABLE_NAME"));
                foreignKeyVo.setFkColumnName(rs.getString("FKCOLUMN_NAME"));

                foreignKeyVo.setKeySeq(rs.getInt("KEY_SEQ"));
                foreignKeyVo.setUpdateRule(rs.getInt("UPDATE_RULE"));
                foreignKeyVo.setDeleteRule(rs.getInt("DELETE_RULE"));

                foreignKeyVo.setPkName(rs.getString("PK_NAME"));
                foreignKeyVo.setFkName(rs.getString("FK_NAME"));


                foreignKeyVo.setDeferrability(rs.getInt("DEFERRABILITY"));

                list.add(foreignKeyVo);
            }
        } finally {
            close(conn);
        }
        return list;
    }
    @Override
    public List execute(Database ds, String sql) throws Exception {
        Statements sqlStmts = CCJSqlParserUtil.parseStatements(sql);
        Connection conn = null;
        List list = Lists.newArrayList();
        try {
            conn = getConnection(ds);
            for (int i = 0; i < sqlStmts.getStatements().size(); i++) {
                ResultSet rs;
                PreparedStatement ps;
                Statement stmt = (Statement) sqlStmts.getStatements().get(i);
                ExecuteVo executeVo = new ExecuteVo();
                String sql1 = stmt.toString();
                executeVo.setSql(sql1);
                ps = conn.prepareStatement(sql1);
                executeVo.setResult("true");
                List list1 = Lists.newArrayList();
                if (stmt instanceof Select ||
                        stmt instanceof ShowStatement) {
                    rs = ps.executeQuery();
                    ResultSetMetaData rsMd = rs.getMetaData();
                    for (int t = 0; t < rsMd.getColumnCount(); t++) {
                        String col_name = rsMd.getColumnName(t + 1);
                        executeVo.getColumns().add(col_name);
                    }
                    while (rs.next()) {
                        Map<String, Object> map = Maps.newLinkedHashMap();
                        for (int t = 0; t < rsMd.getColumnCount(); t++) {
                            Object col_value = rs.getObject(t + 1);
                            map.put(rsMd.getColumnName(t + 1), col_value);
                        }
                        list1.add(map);
                    }

                } else {
                    executeVo.setResult(ps.executeUpdate() == 1 ? "true" : "false");

                }
                executeVo.setData(list1);
                list.add(executeVo);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }
    @Override
    public List execute1(Database ds, String sql) throws Exception {
        Connection conn = null;
        String[] arr = sql.split(";");
        List list = Lists.newArrayList();
        try {
            conn = getConnection(ds);
            for (int i = 0; i < arr.length; i++) {
                ResultSet rs;
                PreparedStatement ps;
                ExecuteVo executeVo = new ExecuteVo();
                String sql1 = arr[i];
                executeVo.setSql(sql1);
                ps = conn.prepareStatement(sql1);
                executeVo.setResult("true");
                List list1 = Lists.newArrayList();
                rs = ps.executeQuery();
                ResultSetMetaData rsMd = rs.getMetaData();
                for (int t = 0; t < rsMd.getColumnCount(); t++) {
                    String col_name = rsMd.getColumnName(t + 1);
                    executeVo.getColumns().add(col_name);
                }
                while (rs.next()) {
                    Map<String, Object> map = Maps.newLinkedHashMap();
                    for (int t = 0; t < rsMd.getColumnCount(); t++) {
                        Object col_value = rs.getObject(t + 1);
                        map.put(rsMd.getColumnName(t + 1), col_value);
                    }
                    list1.add(map);
                }
                executeVo.setData(list1);
                list.add(executeVo);
            }
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Override
    public boolean test(Database ds) throws Exception {
        String conType = ds.getIdDatabaseContype();
        switch (conType) {
            case "Native":
                return testNative(ds);
            default:
                throw new RuntimeException("contType is none");
        }
    }
    @Override
    public boolean testNative(Database ds) throws Exception {
        Connection conn = null;
        try {
            conn = getConnection(ds);
            if (conn != null) {
                return true;
            }
        } finally {
            close(conn);
        }
        return false;
    }
    @Override
    public void valid(Database ds) {
        if (DRIVER_MAP.get(ds.getIdDatabaseType()) == null || PREFIX_MAP.get(ds.getIdDatabaseType()) == null) {
            throw new RuntimeException("clazz or prefix is null");
        }
    }
    @Override
    public Connection getConnection(Database ds) throws Exception {
        String conType = ds.getIdDatabaseContype();
        Connection conn = null;
        switch (conType) {
            case "Native":
                conn = getNativeConnection(ds);
                break;
            default:
                throw new RuntimeException("数据库链接失败");
        }
        return conn;
    }
    @Override
    public Connection getNativeConnection(Database ds) throws Exception {
        String clazz = DRIVER_MAP.get(ds.getIdDatabaseType());
        String prefix = PREFIX_MAP.get(ds.getIdDatabaseType());
        if (clazz == null || prefix == null) {
            throw new RuntimeException("clazz or prefix is null");
        }
        Class.forName(clazz).newInstance();
        return DriverManager.getConnection(getUrl(ds), ds.getUsername(), ds.getPassword());
    }
    @Override
    public String getUrl(Database ds) {
        switch (ds.getIdDatabaseType()) {
            case "MYSQL":
                return PREFIX_MAP.get(ds.getIdDatabaseType()) + ds.getHostName() + ":" + ds.getPort() + "/" + ds.getDatabaseName() + "?useUnicode=true&characterEncoding=UTF-8&useInformationSchema=true";
            default:
                return "";
        }
    }
}
